package com.ltz.dao.base;

import java.io.Serializable;
import java.lang.reflect.Method;
import java.math.BigInteger;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.Table;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import com.ltz.model.Page;

/**
 * @author mr.wang
 * 
 * @param <T>
 *            ����
 * @param <PK>
 *            ����
 */
@Repository("jpaBaseDao")
@Transactional
public class BaseDao{
	protected Logger logger = Logger.getLogger(getClass());

	@PersistenceContext
	protected EntityManager em;
	@Autowired
	protected JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	/**
	 * 自定义spring jdbc查询
	 * @param sql
	 * @param cla
	 * @return
	 */
	public List nativeQueryForList(String sql, Class cla) {
		List list=null;
		try{
			list=jdbcTemplate.query(sql, new BeanPropertyRowMapper(cla));
		}catch(Exception e){
			logger.debug(e.getStackTrace().toString());
			return null;
		}
		return list;
	}
	/**
	 * 自定义spring jdbc查询
	 * @param sql
	 * @param cla
	 * @return
	 */
	public List nativeQueryForList(String sql,Object [] values, Class cla) {
		List list=null;
		try{
			list=jdbcTemplate.query(sql, values,new BeanPropertyRowMapper(cla));
		}catch(Exception e){
			//logger.debug(e.getStackTrace().toString());
			//return null;
			e.printStackTrace();
			System.out.println(e.getStackTrace().toString());
		}
		return list;
	}
	/**
	 * 自定义spring jdbc查询
	 * @param sql
	 * @param cla
	 * @return
	 */
	public Object nativeQueryForObject(String sql, Class cla) {
		try {
			return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(cla));
		} catch (Exception e) {
			logger.debug(e.getStackTrace().toString());
			return null;
		}
	}
	/**
	 * 自定义spring jdbc查询
	 * @param sql
	 * @param cla
	 * @return
	 */
	public Object nativeQueryForObject(String sql,Object [] values,Class cla) {
		try {
			return jdbcTemplate.queryForObject(sql,values, new BeanPropertyRowMapper(cla));
		} catch (Exception e) {
			e.printStackTrace();
			logger.debug(e.getMessage());
			return null;
		}
	}
	public BaseDao() {
	}

	/**
	 *insert
	 */
	public Object add(final Object entity) {
		Assert.notNull(entity, "entityClass");
		em.persist(entity);
		logger.debug("create entity: {}"+entity);
		return entity;	
	}
	/**
	 *update
	 */
	public Object update(final Object entity) {
		Assert.notNull(entity, "entityClass");
		em.merge(entity);
		logger.debug("update entity: {}"+entity);
		return entity;
	}
	/**
	 * delete
	 * @param entity
	 */
	public void delete(final Object entity) {
		Assert.notNull(entity, "entityClass");
		em.remove(em.merge(entity));
		logger.debug("delete entity: {}"+entity);
	}
	/**
	 * delete
	 * @param entityClass
	 * @param id
	 */
	public void delete(Class entityClass,final Integer id) {
		Assert.notNull(id, "id");
		delete(get(entityClass,id));
		logger.debug("delete entity {},id is {}"+entityClass.getName());
	}

	/**
	 * ��id��ȡ����根据Class和id得到对象
	 */
	public Object get(Class entityClass,final Integer id) {
		Assert.notNull(id, "id");
		return em.find(entityClass, id);
	}

	/**
	 * ��ȡȫ������获取所有记录
	 */
	public List getAll(Class entityClass) {
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery c = cb.createQuery(entityClass);
		Root emp = c.from(entityClass);
		c.select(emp);
		TypedQuery q = em.createQuery(c);
		return q.getResultList();
	}

	/**
	 * 查询总数
	 */
	public int getCount(Class entityClass) {
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery c = cb.createQuery(entityClass);
		Root root = c.from(entityClass);
		c.select(cb.countDistinct(root));  
		return ((Number)em.createQuery(c).getSingleResult()).intValue();
	}

	/**
	 *  �����Բ���根据单个属性查询
	 */
	public List findBy(Class entityClass,final String propertyName, final Object value) {
		Assert.hasText(propertyName, "propertyName");
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery c = cb.createQuery(entityClass);
		Root root = c.from(entityClass);
		c.select(root).where(cb.equal(root, root.get(propertyName)));
		TypedQuery q = em.createQuery(c);
		return q.getResultList();
	}

	/**
	 * 自定义jpa查询
	 * @param sql
	 * @param clzss
	 * @param values
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List find(final String sql,Class clzss, final Object[] values) {
		Assert.hasText(sql, "sql����Ϊ��");
		Query query = em.createNativeQuery(sql,clzss);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				Object value = values[i];
				query.setParameter(i+1, value);
			}
		}
		return query.getResultList();
	}
	/**
	 * 根据class和page对象提供的pagesize等参数查询出结果
	 * （jpa查询，根据注释匹配结果集）
	 * @param clzss
	 * @param page
	 * @return 带有结果集的Page对象
	 */
	public Page findByPage(Class clzss, Page page) {
		String tableName = ((Table)(clzss.getAnnotation(Table.class))).name();
		//查询总数
		String countQuery = "select count(*) from " + tableName;
		Query countQ= em.createNativeQuery(countQuery);
		Number count=(Number)countQ.getSingleResult();
		page.setCount(count.intValue());
		page.setPageNum(page.getCount()/page.getPageSize());
		if((page.getCount()%page.getPageSize())>0){
			page.setPageNum(page.getPageNum()+1);
		}
		//查询结果集
//		String sql="select * from " + tableName+" order by create_date desc";
		String sql="select * from " + tableName;
		Query q = em.createNativeQuery(sql,clzss);
		q.setFirstResult(page.getPageSize() * (page.getPageNo() - 1));
		q.setMaxResults(page.getPageSize());
		page.setList(q.getResultList());
		return page;
	}
	/**
	 * 根据class和page对象提供的pagesize等参数查询出结果
	 * （jpa查询，根据注释匹配结果集）
	 * @param page
	 * @param sql
	 * @param values
	 * @return带有结果集的Page对象
	 */
	public Page findByPage(Class clzss,Page page, final String sql,final Object... values) {
		String countSql = prepareCountHql(sql);
		Query countQ = em.createNativeQuery(countSql);
		Query q = em.createNativeQuery(sql, clzss);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				countQ.setParameter(i+1, values[i]);
				q.setParameter(i+1, values[i]);
			}
		}
		//查询总数
		int totalCount = ((Number) countQ.getSingleResult()).intValue();
		page.setCount(totalCount);
		page.setPageNum(page.getCount()/page.getPageSize());
		if((page.getCount()%page.getPageSize())>0){
			page.setPageNum(page.getPageNum()+1);
		}
		q.setFirstResult(page.getPageSize() * (page.getPageNo() - 1));
		q.setMaxResults(page.getPageSize());
		List result = q.getResultList();
		page.setList(result);
		return page;
	}
	/**
	 * 根据自定义本地类查出结果集（jdbcTemplet查询，根据属性名匹配结果集）
	 * @param page
	 * @param sql
	 * @param values
	 * @param clzss
	 * @return
	 */
	public Page findByPage(Page page, final String sql,final Object[] values,Class clzss) {
		String countSql = prepareCountHql(sql);
		Query countQ = em.createNativeQuery(countSql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				countQ.setParameter(i+1, values[i]);
			}
		}
		//查询总数
		int totalCount=0;
		try{
			totalCount = ((Number) countQ.getSingleResult()).intValue();
		}catch(Exception e){
			totalCount =countQ.getResultList().size();
		}
		page.setCount(totalCount);
		page.setPageNum(page.getCount()/page.getPageSize());
		if((page.getCount()%page.getPageSize())>0){
			page.setPageNum(page.getPageNum()+1);
		}
		String querySql=sql+" limit "+(page.getPageSize() * (page.getPageNo() - 1))+","+page.getPageSize();
		List result  =null;
		try {
			result = jdbcTemplate.query(querySql, values, new BeanPropertyRowMapper(clzss));

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		page.setList(result);
		return page;
	}
	public int excute(final String sql,final Object... values){
		Query query = em.createNativeQuery(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				Object value = values[i];
				query.setParameter(i+1, value);
			}
		}
		return query.executeUpdate();
	}
	private String prepareCountHql(String orgHql) {
		String fromHql = orgHql;
		// select子句与order by子句会影响count查询,进行简单的排除.
		fromHql = "from " + StringUtils.substringAfter(fromHql, "from");
		fromHql = StringUtils.substringBefore(fromHql, "order by");
		String countHql = "select count(*) " + fromHql;
		logger.debug("统计数量:{}"+countHql);
		return countHql;
	}
}
